USE [Daco]
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.ProductCategories_GetCategories') AND type in (N'P')))
	DROP PROCEDURE Store.ProductCategories_GetCategories
GO
CREATE PROCEDURE Store.ProductCategories_GetCategories
	@ParentCategoryID	INT,
	@ResultCount		INT OUTPUT
AS
BEGIN
	
	SET @ResultCount = 0
	
	IF(@ParentCategoryID <= 0) SET @ParentCategoryID = NULL
	
	SELECT c.PCategoryID, c.PCategoryTitle, 'Description' = N'' /*ISNULL(Description, N'')*/, 'PParentCategoryID' = ISNULL(c.PParentCategoryID, 0),
		'PParentCategoryTitle' = 
			CASE  
				WHEN c.PParentCategoryID IS NULL THEN N''
				ELSE (SELECT PCategoryTitle FROM Store.ProductCategories WHERE PCategoryID = c.PParentCategoryID)
			END
	FROM Store.ProductCategories c
	WHERE	((@ParentCategoryID IS NOT NULL) OR (c.PParentCategoryID IS NULL)) AND
			((@ParentCategoryID IS NULL) OR (c.PParentCategoryID = @ParentCategoryID))
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.ProductCategories_GetAllCategories') AND type in (N'P')))
	DROP PROCEDURE Store.ProductCategories_GetAllCategories
GO
CREATE PROCEDURE Store.ProductCategories_GetAllCategories
	@ParentCategoryID	INT,
	@ResultCount		INT OUTPUT
AS
BEGIN
	SET @ResultCount = 0
	
	IF(@ParentCategoryID <= 0) 
		SET @ParentCategoryID = NULL;
	
	WITH Categories(PCategoryID, PParentCategoryID, PParentCategoryTitle, PCategoryTitle, [Description], [Level])
	AS
	(
		-- Anchor member definition
		SELECT c.PCategoryID, 'PParentCategoryID' =
			CASE 
				WHEN @ParentCategoryID IS NOT NULL THEN -1	-- ISNULL(c.PParentCategoryID, -1),
				ELSE ISNULL(c.PParentCategoryID, -1)
			END,
			'PParentCategoryTitle' = 
				CASE  
					WHEN c.PParentCategoryID IS NULL THEN N''
					ELSE (SELECT PCategoryTitle FROM Store.ProductCategories WHERE PCategoryID = c.PParentCategoryID)
				END
			, c.PCategoryTitle, 'Description' = N'' /*ISNULL(c.Description, N'')*/, 0 AS Level
		FROM Store.ProductCategories c
		WHERE	((@ParentCategoryID IS NOT NULL) OR (c.PParentCategoryID IS NULL)) AND
				((@ParentCategoryID IS NULL) OR (c.PParentCategoryID = @ParentCategoryID))
				
		UNION ALL
		-- Recursive member definition
		SELECT c.PCategoryID, 'PParentCategoryID' = ISNULL(c.PParentCategoryID, -1),
			--CASE 
			--	WHEN @ParentCategoryID IS NOT NULL THEN -1	-- ISNULL(c.PParentCategoryID, -1),
			--	ELSE ISNULL(c.PParentCategoryID, -1)
			--END,
			'PParentCategoryTitle' = 
				CASE  
					WHEN c.PParentCategoryID IS NULL THEN N''
					ELSE (SELECT PCategoryTitle FROM Store.ProductCategories WHERE PCategoryID = c.PParentCategoryID)
				END
			, c.PCategoryTitle, 'Description' = N'' /*ISNULL(c.Description, N'')*/, Level + 1
		FROM Store.ProductCategories c
		INNER JOIN Categories ON c.PParentCategoryID = Categories.PCategoryID
	)
	-- Statement that executes the CTE
	SELECT PCategoryID, PParentCategoryID, PParentCategoryTitle, PCategoryTitle, Description
	FROM Categories
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.ProductCategories_GetCategory') AND type in (N'P')))
	DROP PROCEDURE Store.ProductCategories_GetCategory
GO
CREATE PROCEDURE Store.ProductCategories_GetCategory
	@CategoryID		INT
AS
BEGIN
	SELECT c.PCategoryID, 'PParentCategoryID' = ISNULL(c.PParentCategoryID, 0),
		'PParentCategoryTitle' = 
			CASE  
				WHEN c.PParentCategoryID IS NULL THEN N''
				ELSE (SELECT PCategoryTitle FROM Store.ProductCategories WHERE PCategoryID = c.PParentCategoryID)
			END
		, c.PCategoryTitle, 'Description' = ISNULL(c.Description, N'')
	FROM Store.ProductCategories c 
	WHERE c.PCategoryID = @CategoryID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.ProductCategories_UpdateCategory') AND type in (N'P')))
	DROP PROCEDURE Store.ProductCategories_UpdateCategory
GO
CREATE PROCEDURE Store.ProductCategories_UpdateCategory
	@CategoryID			INT,
	@ParentCategoryID	INT,
	@CategoryTitle		NVARCHAR(256),
	@Description		NVARCHAR(500)
AS
BEGIN
	IF(@ParentCategoryID <= 0) SET @ParentCategoryID = NULL
	
	UPDATE Store.ProductCategories 
	SET 
		PParentCategoryID = @ParentCategoryID,
		PCategoryTitle = @CategoryTitle,
		Description = @Description	
	WHERE PCategoryID = @CategoryID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.ProductCategories_GetFullTitle') AND type in (N'P')))
	DROP PROCEDURE Store.ProductCategories_GetFullTitle
GO
CREATE PROCEDURE Store.ProductCategories_GetFullTitle
	@ProductCategoryID	INT
AS
BEGIN
	;WITH CategoryTitles(Title, PParentCategoryID) AS
	(
		SELECT PCategoryTitle, PParentCategoryID FROM Store.ProductCategories WHERE PCategoryID = @ProductCategoryID
		UNION ALL
		
		SELECT p.PCategoryTitle, p.PParentCategoryID
		FROM Store.ProductCategories AS p INNER JOIN
			CategoryTitles AS r ON p.PCategoryID = r.PParentCategoryID
		WHERE r.PParentCategoryID IS NOT NULL
	)
	
	SELECT Title FROM CategoryTitles
END
GO
/*
	Product
*/
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.Products_GetProductDescription') AND type in (N'P')))
	DROP PROCEDURE Store.Products_GetProductDescription
GO
CREATE PROCEDURE Store.Products_GetProductDescription
	@ProductDescriptionID INT
AS
BEGIN
	SELECT ProductDescriptionID, ProductID, Title, [Description]
	FROM Store.ProductDescriptions 
	WHERE ProductDescriptionID = @ProductDescriptionID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.Products_GetProduct') AND type in (N'P')))
	DROP PROCEDURE Store.Products_GetProduct
GO
CREATE PROCEDURE Store.Products_GetProduct
	@ProductID		VARCHAR(8),
	@UpdateViewCount	BIT = 0
AS
BEGIN
	SELECT     ProductID, ProductName, AddedDate, ProductSeries, UnitPrice, UnitsInStock, CategoryTitle, EnablePriceConfidential, EnableShopping, CategoryID, 
						  ManufactureID, ManufactureName, UnitMeasure, UnitMeasureCode, Currency, TotalRating, VoteCount, ViewCount, IndexID
	FROM         ViewProducts
	WHERE     (ProductID = @ProductID)
	
	IF(@UpdateViewCount = 1) BEGIN
		UPDATE Store.Products SET ViewCount = ViewCount + 1
		WHERE ProductID = @ProductID
	END
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.Products_GetProductAllDescriptions') AND type in (N'P')))
	DROP PROCEDURE Store.Products_GetProductAllDescriptions
GO
CREATE PROCEDURE Store.Products_GetProductAllDescriptions
	@ProductID		VARCHAR(8)
AS
BEGIN
	SELECT     Store.ProductDescriptions.Description, Store.ProductDescriptions.Title, Store.ProductDescriptions.ProductID, 
						  Store.ProductDescriptions.ProductDescriptionID
	FROM         Store.Products INNER JOIN
						  Store.ProductDescriptions ON Store.Products.ProductID = Store.ProductDescriptions.ProductID
	WHERE     (Store.Products.ProductID = @ProductID)
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.Products_UpdateProductDescription') AND type in (N'P')))
	DROP PROCEDURE Store.Products_UpdateProductDescription
GO
CREATE PROCEDURE Store.Products_UpdateProductDescription
	@ProductDescriptionID	INT,
	@Title			NVARCHAR(256),
	@Description	NVARCHAR(MAX)
AS
BEGIN
	UPDATE Store.ProductDescriptions 
		SET 
			Title = @Title,
			[Description] = @Description
	WHERE ProductDescriptionID = @ProductDescriptionID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.Products_UpdateProductDetail') AND type in (N'P')))
	DROP PROCEDURE Store.Products_UpdateProductDetail
GO
CREATE PROCEDURE Store.Products_UpdateProductDetail
	@ProductID			VARCHAR(15),
	@ManufactureID		INT,
	@ManufactureName	NVARCHAR(256),
	@ProductSeries		VARCHAR(256),
	@PCategoryID		INT,
	@UnitPrice			MONEY,
	@Currency			VARCHAR(3),
	@UnitMeasureCode	VARCHAR(3),
	@UnitsInStock		INT,
	@EnablePriceConfidential	BIT,
	@EnableShopping		BIT
AS
BEGIN
	IF(@ManufactureID = 0) BEGIN
		INSERT INTO Store.ProductManufacturers (ManName)
		VALUES (@ManufactureName)
		
		SELECT @ManufactureID = SCOPE_IDENTITY()
	END
	
	UPDATE Store.Products SET
		PCategoryID = @PCategoryID,
		ManufactureID	= @ManufactureID,
		ProductSeries	= @ProductSeries,
		UnitPrice		= @UnitPrice,
		Currency		= @Currency,
		UnitMeasureCode = @UnitMeasureCode,
		UnitsInStock	= @UnitsInStock,
		EnablePriceConfidential = @EnablePriceConfidential,
		EnableShopping			= @EnableShopping
	WHERE Store.Products.ProductID = @ProductID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.Products_AddProduct') AND type in (N'P')))
	DROP PROCEDURE Store.Products_AddProduct
GO
CREATE PROCEDURE Store.Products_AddProduct
	@ProductID				VARCHAR(8),
	@ProductSeries			VARCHAR(256),
	@ManufactureID			INT,
	@ManufactureName		NVARCHAR(256),
	@PCategoryID			INT,
	@PCategoryTitle			NVARCHAR(256),
	@UnitMeasureCode		VARCHAR(3),
	@UnitPrice				MONEY,
	@EnablePriceConfidential BIT = 0,
	@EnableShopping			BIT = 0,
	@Currency				VARCHAR(3),
	@UnitsInStock			INT
AS
BEGIN
	IF(NOT EXISTS(SELECT * FROM Store.ProductCategories WHERE PCategoryID = @PCategoryID)) BEGIN
		INSERT INTO Store.ProductCategories(PCategoryTitle, Description)
		VALUES (@PCategoryTitle, N'')
		
		SELECT @PCategoryID = SCOPE_IDENTITY()
	END
	
	INSERT INTO Store.Products (ProductID, ManufactureID, ProductSeries, UnitPrice, EnablePriceConfidential, EnableShopping, Currency, UnitMeasureCode, UnitsInStock)
	VALUES (@ProductID,  @ManufactureID, @ProductSeries, @UnitPrice, @EnablePriceConfidential, @EnableShopping, @Currency, @UnitMeasureCode, @UnitsInStock)	
	
	RETURN 0
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.Products_GetProducts') AND type in (N'P')))
	DROP PROCEDURE Store.Products_GetProducts
GO
CREATE PROCEDURE Store.Products_GetProducts
	@PageIndex		INT,
	@PageSize		INT,
	@OUTPUT_VALUE	INT OUTPUT
AS
BEGIN
	DECLARE @PageIndexForProducts TABLE
	(
		IndexID INT NOT NULL,
		ResultCount	INT
	)
	
	DECLARE @PageLowerBound INT
		SET @PageLowerBound = (@PageIndex - 1) * @PageSize
	DECLARE @PageUpperBound INT
		SET @PageUpperBound = @PageIndex * @PageSize - 1
		
	;WITH CTEProducts(IndexID, NumRow) AS
	(
		SELECT IndexID, ROW_NUMBER() OVER(ORDER BY AddedDate DESC) AS NumRow 
		FROM ViewProducts
	)
	
	SELECT v.* FROM CTEProducts INNER JOIN ViewProducts AS v ON v.IndexID = CTEProducts.IndexID WHERE NumRow BETWEEN (@PageLowerBound + 1) AND (@PageUpperBound + 1)
	SELECT @OUTPUT_VALUE = COUNT(*) FROM ViewProducts
	
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.Products_GetNewestProducts') AND type in (N'P')))
	DROP PROCEDURE Store.Products_GetNewestProducts
GO
CREATE PROCEDURE Store.Products_GetNewestProducts
	@ItemCount	INT
AS
BEGIN
	SET ROWCOUNT @ItemCount
	
	SELECT v.* FROM ViewProducts AS v ORDER BY v.AddedDate DESC
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.Products_GetRelatedProducts') AND type in (N'P')))
	DROP PROCEDURE Store.Products_GetRelatedProducts
GO
CREATE PROCEDURE Store.Products_GetRelatedProducts
	@ProductID	VARCHAR(15),
	@PCategoryID	INT,
	@ManufactureID	INT,
	@ItemCount	INT
AS
BEGIN
	DECLARE @MAX_ITEM_COUNT	INT
		SET @MAX_ITEM_COUNT = 12
		
	IF(@ItemCount > @MAX_ITEM_COUNT)
		SET @ItemCount = @MAX_ITEM_COUNT
		
	DECLARE @CurrentItemCount	INT
		SET @CurrentItemCount = 0
		
	DECLARE @RemainItemCount	INT
		SET @RemainItemCount = @ItemCount
		
	DECLARE @RelatedProducts TABLE
	(
		ProductID	VARCHAR(15)
	)
	
	SET ROWCOUNT @RemainItemCount
	
	INSERT INTO @RelatedProducts(ProductID)
		SELECT p.ProductID
		FROM Store.Products AS p
		WHERE 
			p.ManufactureID = p.ManufactureID AND
			p.PCategoryID = @PCategoryID AND
			p.ProductID <> @ProductID
	
	SELECT @CurrentItemCount = COUNT(ProductID) FROM @RelatedProducts
	IF(@CurrentItemCount < @ItemCount) BEGIN
		SET @RemainItemCount = @ItemCount - @CurrentItemCount
		
		SET ROWCOUNT @RemainItemCount
		
		INSERT INTO @RelatedProducts(ProductID)
			SELECT p.ProductID
			FROM Store.Products AS p
			WHERE 
				p.PCategoryID = @PCategoryID AND
				p.ProductID <> @ProductID AND p.ProductID NOT IN (SELECT ProductID FROM @RelatedProducts)
	END
	
	SELECT v.* 
	FROM ViewProducts AS v INNER JOIN @RelatedProducts AS r ON v.ProductID = r.ProductID
	ORDER BY v.AddedDate
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.Products_GetFavoriteProducts') AND type in (N'P')))
	DROP PROCEDURE Store.Products_GetFavoriteProducts
GO
CREATE PROCEDURE Store.Products_GetFavoriteProducts
	@ItemCount	INT
AS
BEGIN
	SET ROWCOUNT @ItemCount
	
	SELECT v.* 
	FROM ViewProducts AS v
	ORDER BY v.TotalRating DESC, v.ViewCount DESC
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.Products_IncrementViewCount') AND type in (N'P')))
	DROP PROCEDURE Store.Products_IncrementViewCount
GO
CREATE PROCEDURE Store.Products_IncrementViewCount
	@ProductID	VARCHAR(15)
AS
BEGIN
	UPDATE Store.Products
	SET
		ViewCount = ViewCount + 1
	WHERE ProductID = @ProductID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.Products_GetProductsByCategory') AND type in (N'P')))
	DROP PROCEDURE Store.Products_GetProductsByCategory
GO
CREATE PROCEDURE Store.Products_GetProductsByCategory
	@ProductCategoryID	INT,
	@PageIndex		INT,
	@PageSize		INT,
	@OUTPUT_VALUE	INT OUTPUT
AS
BEGIN

	DECLARE @PageIndexForProducts TABLE
	(
		ProductID VARCHAR(15),
		TotalCount INT
	)
	
	DECLARE @PageLowerBound INT
		SET @PageLowerBound = (@PageIndex - 1) * @PageSize
	DECLARE @PageUpperBound INT
		SET @PageUpperBound = @PageIndex * @PageSize - 1

	;WITH CTEProducts(ProductID, AddedDate, CategoryID, IndexID, TotalCount)
	AS
	(
		SELECT     ProductID, AddedDate, CategoryID, ROW_NUMBER() OVER(ORDER BY AddedDate DESC) AS IndexID, COUNT(ProductID) OVER() AS TotalCount
		FROM         ViewProducts
		WHERE CategoryID = @ProductCategoryID
		--ORDER BY AddedDate DESC
		
		UNION ALL
		
		SELECT     v.ProductID, v.AddedDate, v.CategoryID, (e.TotalCount + ROW_NUMBER() OVER(ORDER BY v.AddedDate DESC)) AS IndexID, (e.TotalCount + COUNT(v.ProductID) OVER()) AS TotalCount
		FROM         ViewProducts AS v
			INNER JOIN Store.ProductCategories AS c ON c.PParentCategoryID = v.CategoryID
			INNER JOIN CTEProducts AS e ON e.CategoryID = c.PParentCategoryID 
		--ORDER BY v.AddedDate DESC
	)

	INSERT INTO @PageIndexForProducts(ProductID, TotalCount)
	SELECT ProductID, TotalCount FROM CTEProducts
	WHERE IndexID BETWEEN @PageLowerBound AND @PageUpperBound

	SELECT TOP 1 @OUTPUT_VALUE = TotalCount FROM @PageIndexForProducts

	SELECT v.*
	FROM ViewProducts AS v INNER JOIN @PageIndexForProducts AS p ON p.ProductID = v.ProductID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.Products_GetFilterProducts') AND type in (N'P')))
	DROP PROCEDURE Store.Products_GetFilterProducts
GO
CREATE PROCEDURE Store.Products_GetFilterProducts
	@ProductCategoryIDList	VARCHAR(1000),
	@ManufactureIDList		VARCHAR(1000),
	@ProductSeries	VARCHAR(50),
	@ProductName	NVARCHAR(256),
	@PriceFrom		MONEY,
	@PriceTo		MONEY,
	@PageIndex		INT,
	@PageSize		INT,
	@OUTPUT_VALUE	INT OUTPUT
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @FilterCategoryCount INT
	DECLARE @FilterManufactureCount INT
	DECLARE @FilterProductSeriesCount INT
	
	DECLARE @PageLowerBound INT
		SET @PageLowerBound = (@PageIndex - 1) * @PageSize + 1
	DECLARE @PageUpperBound INT
		SET @PageUpperBound = @PageIndex * @PageSize
	
	DECLARE @FilterCategories TABLE
	(
		ProductCategoryID INT
	)
	
	INSERT INTO @FilterCategories(ProductCategoryID)
		SELECT c.PCategoryID
		FROM Store.ProductCategories AS c 
			INNER JOIN dbo.Split(@ProductCategoryIDList, ',') AS s ON c.PCategoryID = CONVERT(INT, s.ItemId)
	
	SELECT @FilterCategoryCount = COUNT(ProductCategoryID) FROM @FilterCategories
	
	IF(@FilterCategoryCount = 0)
		INSERT INTO @FilterCategories(ProductCategoryID)
			SELECT c.PCategoryID
			FROM Store.ProductCategories AS c 
	
	DECLARE @FilterManufactures TABLE
	(
		ManufactureID	INT
	)
	
	INSERT INTO @FilterManufactures(ManufactureID)
		SELECT c.ManID
		FROM Store.ProductManufacturers AS c 
			INNER JOIN dbo.Split(@ManufactureIDList, ',') AS s ON c.ManID = CONVERT(INT, s.ItemId)
			
	SELECT @FilterManufactureCount = COUNT(ManufactureID) FROM @FilterManufactures
	
	IF(@FilterManufactureCount = 0)
		INSERT INTO @FilterManufactures(ManufactureID)
			SELECT c.ManID
			FROM Store.ProductManufacturers AS c 
			
	DECLARE @FilterProductSeries TABLE
	(
		ProductSeries VARCHAR(50)
	)
	
	INSERT INTO @FilterProductSeries(ProductSeries)
		SELECT ItemID FROM dbo.Split(@ProductSeries, ',')
	
	SELECT @FilterProductSeriesCount = COUNT(ProductSeries) FROM @FilterProductSeries
	
	DECLARE @FilterProducts TABLE
	(
		ProductID VARCHAR(15),
		ResultCount INT
	)
	
	IF(@FilterProductSeriesCount > 0) BEGIN
		;WITH FilterProducts(ProductID, RowNum, ResultCount) AS
		(
			SELECT v.ProductID, ROW_NUMBER() OVER(ORDER BY v.ProductID) AS RowNum, COUNT(v.ProductID) OVER() AS ResultCount
			FROM Store.Products AS v
				INNER JOIN @FilterCategories AS fc ON v.PCategoryID = fc.ProductCategoryID
				INNER JOIN @FilterManufactures AS fm ON v.ManufactureID = fm.ManufactureID
				INNER JOIN @FilterProductSeries AS fp ON v.ProductSeries LIKE  '%' + fp.ProductSeries + '%'
			WHERE v.UnitPrice >= @PriceFrom AND v.UnitPrice <= @PriceTo
				AND (LEN(@ProductName) <= 3 OR v.ProductName LIKE '%' + @ProductName + '%')
		)
		INSERT INTO @FilterProducts(ProductID, ResultCount)
			SELECT ProductID, ResultCount FROM FilterProducts WHERE RowNum >= @PageLowerBound AND RowNum <= @PageUpperBound
	END
	ELSE BEGIN
		;WITH FilterProducts(ProductID, RowNum, ResultCount) AS
		(
			SELECT v.ProductID, ROW_NUMBER() OVER(ORDER BY v.ProductID) AS RowNum, COUNT(v.ProductID) OVER() AS ResultCount
			FROM Store.Products AS v
				INNER JOIN @FilterCategories AS fc ON v.PCategoryID = fc.ProductCategoryID
				INNER JOIN @FilterManufactures AS fm ON v.ManufactureID = fm.ManufactureID
			WHERE v.UnitPrice >= @PriceFrom AND v.UnitPrice <= @PriceTo
				AND (LEN(@ProductName) <= 3 OR v.ProductName LIKE '%' + @ProductName + '%')
		)
		INSERT INTO @FilterProducts(ProductID, ResultCount)
			SELECT ProductID, ResultCount FROM FilterProducts WHERE RowNum >= @PageLowerBound AND RowNum <= @PageUpperBound
	END
	
	SELECT TOP 1 @OUTPUT_VALUE = ResultCount FROM @FilterProducts
	
	SELECT v.* 
	FROM ViewProducts AS v INNER JOIN @FilterProducts AS fp ON v.ProductID = fp.ProductID
END
GO

/*
	Store
*/
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.GetUnitMeasureTable') AND type in (N'P')))
	DROP PROCEDURE Store.GetUnitMeasureTable
GO
CREATE PROCEDURE Store.GetUnitMeasureTable
AS
BEGIN
	
	SELECT     UnitMeasureCode, Name
	FROM         Store.UnitMeasure
	ORDER BY	Name
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.ProductManufactures_GetManufacture') AND type in (N'P')))
	DROP PROCEDURE Store.ProductManufactures_GetManufacture
GO
CREATE PROCEDURE Store.ProductManufactures_GetManufacture
	@ManufactureID	INT
AS
BEGIN
	SELECT     ManName AS Name, ManID as ManufactureID, [Description]
	FROM         Store.ProductManufacturers
	WHERE ManID = @ManufactureID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.ProductManufactures_GetManufactureTable') AND type in (N'P')))
	DROP PROCEDURE Store.ProductManufactures_GetManufactureTable
GO
CREATE PROCEDURE Store.ProductManufactures_GetManufactureTable
AS
BEGIN
	SELECT     ManName AS Name, ManID as ManufactureID--, [Description]
	FROM         Store.ProductManufacturers
	ORDER BY Name	
END
GO

--IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.GetCurrencyTable') AND type in (N'P')))
--	DROP PROCEDURE Store.GetCurrencyTable
--GO
--CREATE PROCEDURE Store.GetCurrencyTable
--AS
--BEGIN
--	SELECT     CurrencyCode, CountryRegionCode, Name
--	FROM         Store.Currencies
--	ORDER BY CurrencyCode	
--END
--GO

/*
	Specialization
*/
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.AddProductSpecialization') AND type in (N'P')))
	DROP PROCEDURE Store.AddProductSpecialization
GO
CREATE PROCEDURE Store.AddProductSpecialization
	@AttachmentID		NVARCHAR(8),
	@ProductCategoryID	INT,
	@Title				NVARCHAR(256),
	@FileName			NVARCHAR(256),
	@FileExtension		VARCHAR(6),
	@ContentType		VARCHAR(50),
	@Content			VARBINARY(MAX)
AS
BEGIN
	INSERT INTO Attachments (AttachmentID, CategoryID, Title, FileName, FileExtension, ContentType, [Content])
	VALUES (@AttachmentID, @ProductCategoryID, @Title, @FileName, @FileExtension, @ContentType, @Content)
END
GO

/*
	Photo
*/
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Store.Photos_AddPhoto') AND type IN (N'P')))
	DROP PROCEDURE Store.Photos_AddPhoto
GO
CREATE PROCEDURE Store.Photos_AddPhoto
	@PhotoID				VARCHAR(8),
	@ProductID				VARCHAR(15),
	@Title					NVARCHAR(256),
	@ThumbPhotoFileName		NVARCHAR(256),
	@ThumbPhoto				VARBINARY(MAX),
	@LargePhotoFileName		NVARCHAR(256),
	@LargePhoto				VARBINARY(MAX),
	@Primary				BIT
AS
BEGIN
	DECLARE @ErrorCode INT
		SET @ErrorCode = 0
	DECLARE @TranStarted   bit
    SET @TranStarted = 0

    IF( @@TRANCOUNT = 0 )
    BEGIN
	    BEGIN TRANSACTION
	    SET @TranStarted = 1
    END
    ELSE
    	SET @TranStarted = 0
    	
	INSERT INTO Store.Photos(PhotoID, ProductID, Title, ThumbnailPhotoFileName, ThumbnailPhoto, LargePhotoFileName, LargePhoto, [Primary])
	VALUES (@PhotoID, @PhotoID, @Title, @ThumbPhotoFileName, @ThumbPhoto, @LargePhotoFileName, @LargePhoto, @Primary)
	
	IF(@@ERROR <> 0) GOTO CleanUp
	
	IF( @TranStarted = 1 )
    BEGIN
	    SET @TranStarted = 0
	    COMMIT TRANSACTION
    END

    RETURN 0

CleanUp:
	SET @ErrorCode = @@ERROR
	
    IF( @TranStarted = 1 )
    BEGIN
        SET @TranStarted = 0
    	ROLLBACK TRANSACTION
    END

    RETURN @ErrorCode
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Store.Photos_UpdatePhoto') AND type IN (N'P')))
	DROP PROCEDURE Store.Photos_UpdatePhoto
GO
CREATE PROCEDURE Store.Photos_UpdatePhoto
	@PhotoID			VARCHAR(8),
	@Title				NVARCHAR(256),
	@ThumbPhotoFileName	NVARCHAR(256),
	@ThumbPhoto			VARBINARY(MAX),
	@LargePhotoFileName	NVARCHAR(256),
	@LargePhoto			VARBINARY(MAX),
	@Primary			BIT
AS
BEGIN
	UPDATE Store.Photos 
	SET
		Title					= @Title,
		ThumbnailPhotoFileName	= @ThumbPhotoFileName,
		ThumbnailPhoto			= @ThumbPhoto,
		LargePhotoFileName		= @LargePhotoFileName,
		LargePhoto				= @LargePhoto,
		[Primary]				= @Primary
	WHERE PhotoID = @PhotoID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Store.Photos_GetPhotoContent') AND type IN (N'P')))
	DROP PROCEDURE Store.Photos_GetPhotoContent
GO
CREATE PROCEDURE Store.Photos_GetPhotoContent
	@PhotoID	VARCHAR(8),
	@IsLargePhoto	BIT = 0
AS
BEGIN
	IF(@IsLargePhoto = 0)
		SELECT ThumbnailPhoto FROM Store.Photos WHERE PhotoID = @PhotoID
	ELSE
		SELECT LargePhoto FROM Store.Photos WHERE PhotoID = @PhotoID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Store.Photos_GetPhotos') AND type IN (N'P')))
	DROP PROCEDURE Store.Photos_GetPhotos
GO
CREATE PROCEDURE Store.Photos_GetPhotos
	@ProductID	VARCHAR(15)
AS
BEGIN
	SELECT p.PhotoID, @ProductID, p.Title, p.ThumbnailPhotoFileName, p.LargePhotoFileName, p.[Primary]
	FROM Store.Photos p INNER JOIN 
		 Store.Products h ON h.ProductID = p.ProductID
	WHERE h.ProductID = @ProductID
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.Photos_DeletePhoto') AND type IN (N'P')))
	DROP PROCEDURE dbo.Photos_DeletePhoto
GO
CREATE PROCEDURE dbo.Photos_DeletePhoto
	@PhotoID	VARCHAR(8)
AS
BEGIN
	DELETE Store.Photos WHERE PhotoID = @PhotoID
END
GO
/*
	ProductReview
*/
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.Products_GetProductReviews') AND type in (N'P')))
	DROP PROCEDURE Store.Products_GetProductReviews
GO
CREATE PROCEDURE Store.Products_GetProductReviews
	@ProductID		VARCHAR(8),
	@Approved		BIT,
	@PageIndex		INT,
	@PageSize		INT,
	@RESULT_COUNT	INT OUTPUT
AS
BEGIN
	DECLARE @PageIndexForProductReivew TABLE
	(
		IndexID		INT IDENTITY(0,1),
		ProductReviewID	INT
	)

	DECLARE @PageLowerBound	INT
		SET @PageLowerBound = (@PageIndex - 1) * @PageSize
	DECLARE @PageUpperBound INT
		SET @PageUpperBound = @PageLowerBound + @PageSize - 1
		
	INSERT INTO @PageIndexForProductReivew (ProductReviewID)
		SELECT     ProductReviewID
		FROM         Store.ProductReviews
		WHERE     (ProductID = @ProductID) AND (IsApproved = @Approved)
		ORDER BY ReviewDate DESC
		
	SELECT COUNT(*) FROM @PageIndexForProductReivew
	
	SELECT     pv.ProductReviewID, pv.IsApproved, pv.PReviewerName AS ReviewerName, pv.Email AS ReviewerEmail, pv.IPAddress, pv.Rating, pv.Comments
	FROM		@PageIndexForProductReivew p INNER JOIN 
		         Store.ProductReviews AS pv ON pv.ProductReviewID = p.ProductReviewID
	WHERE	p.IndexID BETWEEN @PageLowerBound AND @PageUpperBound
END
GO
IF(EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id(N'Store.Products_GetProductReviewCount') AND type in (N'P')))
	DROP PROCEDURE Store.Products_GetProductReviewCount
GO
CREATE PROCEDURE Store.Products_GetProductReviewCount
	@ProductID		VARCHAR(8),
	@Approved		BIT
AS
BEGIN
	SELECT COUNT(*) FROM Store.ProductReviews WHERE ProductID = @ProductID AND IsApproved = @Approved
END
GO